package com.leminhtrinh.mycash.model;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class CashDataSource {

	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] transactionAllColumns = { MySQLiteHelper.COLUMN_ID,
			MySQLiteHelper.COLUMN_VALUE, MySQLiteHelper.COLUMN_CATEGORY,
			MySQLiteHelper.COLUMN_ACCOUNT, MySQLiteHelper.COLUMN_PARTNER,
			MySQLiteHelper.COLUMN_DESCRIPTION, MySQLiteHelper.COLUMN_INOUT,
			MySQLiteHelper.COLUMN_YEAR, MySQLiteHelper.COLUMN_MONTH,
			MySQLiteHelper.COLUMN_DAY, MySQLiteHelper.COLUMN_WEEKOFYEAR };

	private String[] accountAllColumns = { MySQLiteHelper.COLUMN_ID,
			MySQLiteHelper.COLUMN_ACC_NAME, MySQLiteHelper.COLUMN_ACC_BUDGET };
	private String[] categoryAllColumns = { MySQLiteHelper.COLUMN_ID,
			MySQLiteHelper.COLUMN_ACC_NAME, MySQLiteHelper.COLUMN_ACC_BUDGET };

	/**
	 * Constructor
	 * 
	 * @param context
	 */
	public CashDataSource(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	/**
	 * Open database
	 * 
	 * @throws SQLException
	 */
	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	/**
	 * Close database
	 */
	public void close() {
		dbHelper.close();
	}

	/**
	 * create a object Transaction
	 * 
	 * @param value
	 * @param category
	 * @param account
	 * @param partner
	 * @param description
	 * @param inOutValue
	 * @param year
	 * @param month
	 * @param day
	 * @param weekOfYear
	 * @return
	 */
	public CashTransaction createTransaction(double value, long category,
			long account, String partner, String description, int inOutValue,
			int year, int month, int day, int weekOfYear) {
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.COLUMN_VALUE, Double.toString(value));
		values.put(MySQLiteHelper.COLUMN_CATEGORY, category);
		values.put(MySQLiteHelper.COLUMN_ACCOUNT, account);
		values.put(MySQLiteHelper.COLUMN_PARTNER, partner);
		values.put(MySQLiteHelper.COLUMN_DESCRIPTION, description);
		values.put(MySQLiteHelper.COLUMN_INOUT, Integer.toString(inOutValue));
		values.put(MySQLiteHelper.COLUMN_YEAR, Integer.toString(year));
		values.put(MySQLiteHelper.COLUMN_MONTH, Integer.toString(month));
		values.put(MySQLiteHelper.COLUMN_DAY, Integer.toString(day));
		values.put(MySQLiteHelper.COLUMN_WEEKOFYEAR,
				Integer.toString(weekOfYear));
		long insertIdTrans = database.insert(MySQLiteHelper.TABLE_TRANSACTION,
				null, values);
		Cursor cursor = database.query(MySQLiteHelper.TABLE_TRANSACTION,
				transactionAllColumns, MySQLiteHelper.COLUMN_ID + " = "
						+ insertIdTrans, null, null, null, null);
		cursor.moveToFirst();
		CashTransaction newTransaction = cursorToTransaction(cursor);
		cursor.close();
		return newTransaction;
	}

	public CashAccount createAccount(String name, double budget) {
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.COLUMN_ACC_NAME, name);
		values.put(MySQLiteHelper.COLUMN_ACC_BUDGET, budget);
		long insertIdAcc = database.insert(MySQLiteHelper.TABLE_ACCOUNT, null,
				values);
		Cursor cursor = database.query(MySQLiteHelper.TABLE_ACCOUNT,
				transactionAllColumns, MySQLiteHelper.COLUMN_ID + " = "
						+ insertIdAcc, null, null, null, null);
		cursor.moveToFirst();
		CashAccount newAccount = cursorToAccount(cursor);
		cursor.close();
		return newAccount;
	}

	public CashCategory createCategory(String name, double budget) {
		ContentValues values = new ContentValues();
		values.put(MySQLiteHelper.COLUMN_CAT_NAME, name);
		values.put(MySQLiteHelper.COLUMN_CAT_BUDGET, budget);
		long insertIdAcc = database.insert(MySQLiteHelper.TABLE_CATEGORY, null,
				values);
		Cursor cursor = database.query(MySQLiteHelper.TABLE_ACCOUNT,
				transactionAllColumns, MySQLiteHelper.COLUMN_ID + " = "
						+ insertIdAcc, null, null, null, null);
		cursor.moveToFirst();
		CashCategory newCategory = cursorToCategory(cursor);
		cursor.close();
		return newCategory;
	}

	/**
	 * delete a transaction
	 * 
	 * @param transaction
	 *            ID
	 */
	public void deleteTransaction(CashTransaction transaction) {
		long id = transaction.getId();
		System.out.println("Activity deleted with id: " + id);
		database.delete(MySQLiteHelper.TABLE_TRANSACTION,
				MySQLiteHelper.COLUMN_ID + " = " + id, null);
	}

	public void deleteAccount(CashAccount acc) {
		long id = acc.getId();
		System.out.println("Account deleted with id: " + id);
		database.delete(MySQLiteHelper.TABLE_ACCOUNT, MySQLiteHelper.COLUMN_ID
				+ " = " + id, null);
	}

	public void deleteCategory(CashCategory category) {
		long id = category.getId();
		System.out.println("Category deleted with id: " + id);
		database.delete(MySQLiteHelper.TABLE_CATEGORY, MySQLiteHelper.COLUMN_ID
				+ " = " + id, null);
	}

	/**
	 * get all transaction from database
	 * 
	 * @return
	 */
	public List<CashTransaction> getAllTransactions() {
		List<CashTransaction> transactions = new ArrayList<CashTransaction>();

		Cursor cursor = database.query(MySQLiteHelper.TABLE_TRANSACTION,
				transactionAllColumns, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			CashTransaction transaction = cursorToTransaction(cursor);
			transactions.add(transaction);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return transactions;
	}

	public List<CashAccount> getAllAccounts() {
		List<CashAccount> accs = new ArrayList<CashAccount>();

		Cursor cursor = database.query(MySQLiteHelper.TABLE_ACCOUNT,
				accountAllColumns, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			CashAccount acc = cursorToAccount(cursor);
			accs.add(acc);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return accs;
	}

	public List<CashCategory> getAllCategories() {
		List<CashCategory> cats = new ArrayList<CashCategory>();

		Cursor cursor = database.query(MySQLiteHelper.TABLE_CATEGORY,
				categoryAllColumns, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			CashCategory cat = cursorToCategory(cursor);
			cats.add(cat);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return cats;
	}

	/**
	 * convert from database to object
	 * 
	 * @param cursor
	 * @return
	 */
	private CashTransaction cursorToTransaction(Cursor cursor) {
		CashTransaction transaction = new CashTransaction();
		transaction.setId(cursor.getLong(0));
		transaction.setValue(cursor.getDouble(1));
		transaction.setCategory_id(cursor.getLong(2));
		transaction.setPartner(cursor.getString(3));
		transaction.setDescription(cursor.getString(4));
		transaction.setInOutValue(cursor.getInt(5));
		transaction.setYear(cursor.getInt(6));
		transaction.setMonth(cursor.getInt(7));
		transaction.setDay(cursor.getInt(8));
		transaction.setWeekOfYear(cursor.getInt(9));
		return transaction;
	}
	
	private CashAccount cursorToAccount(Cursor cursor) {
		CashAccount acc = new CashAccount();
		acc.setId(cursor.getLong(0));
		acc.setName(cursor.getString(1));
		acc.setBudget(cursor.getDouble(2));
		return acc;
	}
	
	private CashCategory cursorToCategory(Cursor cursor) {
		CashCategory cat = new CashCategory();
		cat.setId(cursor.getLong(0));
		cat.setName(cursor.getString(1));
		cat.setBudget(cursor.getDouble(2));
		return cat;
	}
}
